<?php

namespace App\Http\Controllers;


use App\Models\ProductTypes;
use GuzzleHttp\Client;
use Illuminate\Http\Request;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;


class MigrateController extends Controller
{
    /*--
     * 查询导出数据*/
    public function export303()
    {
        $id = 101111111;
        $query = <<<EOF
SELECT 
       w.warranty_id,
       w.first_name,
       c.simplified_chiness_value as region_name, 
       d.simplified_chiness_value as province_name, 
       e.simplified_chiness_value as city, 
       w.address, 
       w.phone_number, 
       u.unique_id, 
       u.company_name, 
       w.installer_name, 
       w.installation_date, 
       w.installation_price, 
       w.year_id,
       w.model_id,
       f.simplified_chiness_value as make_name, 
       w.vin_number, 
       w.license_plate,
       b.roll_number,
       g.simplified_chiness_value as part_name,
       h.simplified_chiness_value as film_type_name,
       b.length,
       b.width,
       w.additional_comments,
       if(b.precut_or_manual!=1, '手工', '裁切') as precut_or_manual

FROM warranty as w
LEFT JOIN users as u on w.user_id=u.id
LEFT JOIN warranty_to_vehicle_coverage as b on w.id=b.warranty_id
LEFT JOIN region_master as c  on c.id=w.region_id
LEFT JOIN province_master as d on d.id=w.province_id
LEFT JOIN cities as e on e.id=w.city
LEFT JOIN make_master as f ON f.id=w.make_id
LEFT JOIN part_master as g ON g.id=b.part_id
LEFT JOIN film_type_master as h ON h.id=b.film_type_id
WHERE (w.id >= '{$id}')
ORDER BY w.warranty_id
EOF;

        $e = DB::connection('newWarranty')->select($query);
        $res = [];
        foreach ($e as $k => $v) {
            $item = [];
            foreach ($v as $ak => $av) {
                $text = $av;
                $item[$ak] = $text;
            }
            $res[] = $item;
        }
        $field = [
            'warranty_id',
            'first_name',
            'region_name',
            'province_name',
            'city',
            'address',
            'phone_number',
            'unique_id',
            'company_name',
            'installer_name',
            'installation_date',
            'installation_price',
            'year_id',
            'model_id',
            'make_name',
            'vin_number',
            'license_plate',
            'part_name',
            'precut_or_manual',
            'film_type_name',
            'roll_number',
            'length',
            'width',
            'additional_comments'
        ];
        $data = [];
        foreach ($res as $k => $v) {
            $item = [];
            foreach ($field as $f) {
                $item[] = [
                    'label' => $f,
                    'value' => $v[$f],
                ];
            }
            $data[] = $item;
        }

        $name = '质保导出-'.date('YmdHis');
        $this->makeExcel2($data, $name);
        return [];
    }

    /*
     * 导出为EXCEL*/
    public static function makeExcel2($data,$filename=null)
    {

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        if (empty($data)) {
        } else {
            foreach ($data as $k => $v) {
                $value = Arr::pluck($v, 'value');
                $sheet->fromArray($value, NULL, 'A'.(1+$k));
            }
        }
        $filename = $filename ?: time().'.xlsx';
        $filename = $filename . '-' . count($data);
        $filename = basename($filename, '.xlsx').'.xlsx';
        $path = __DIR__.'/20201209/';
        if (!file_exists($path)) {
            mkdir($path);
        }
        $temp_file = $path.$filename;
        $writer->save($temp_file);
    }


    /*
     * 更新导入数据的质保ID、卷号剩余长度信息*/
    public function ib2()
    {
        $wid = '101111111';
        $wvc = DB::connection('newWarranty')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'w.id', 'wvc.warranty_id')
            ->where('w.id', '>=', $wid)
            ->where('w.warranty_id', 0)
            ->get(['w.id', 'w.user_id', 'wvc.roll_number', 'wvc.length']);

        DB::connection('newWarranty')
            ->table('warranty')
            ->where('id', '>=', $wid)
            ->update([
                'warranty_id' => DB::raw('id')
            ]);

        $rl = [];
        foreach ($wvc as $k => $v) {

            $rl = $this->minusLength($v->user_id, $v->roll_number, $v->length);
        }
        return [];
    }

    public function showUpload()
    {
        return view('migrate.upload');
    }

    /*
     * 批量导入*/
    public function insertB(Request $request)
    {

    }

    public function insertData()
    {

    }

    public function updateData()
    {

    }

    /*
     * 减少剩余长度*/
    public function minusLength($user_id, $roll_number, $length)
    {
        $this->FOC($user_id, $roll_number);
        $rl = DB::connection('newWarranty')
            ->table('roll_restlen')
            ->where([
                'user_id'=>$user_id,
                'roll_number' => $roll_number,
            ])
            ->update([
                'restlen' => DB::raw("restlen - ".$length)
            ]);
        return $rl;
    }


    /*
     * 增加剩余长度*/
    public function plusLength($user_id, $roll_number, $length)
    {
        $this->FOC($user_id, $roll_number);
        $rl = DB::connection('newWarranty')
            ->table('roll_restlen')
            ->where([
                'user_id'=>$user_id,
                'roll_number' => $roll_number,
            ])
            ->update([
                'restlen' => DB::raw("restlen + ".$length)
            ]);
        return $rl;
    }

    /*
     * 剩余长度记录*/
    public function FOC($user_id, $roll_number)
    {
        $rl = DB::connection('newWarranty')
            ->table('roll_restlen')
            ->where([
                'user_id'=>$user_id,
                'roll_number' => $roll_number,
            ])
            ->first();
        if (empty($rl->id)) {
            $data = [
                'user_id' => $user_id,
                'roll_number' => $roll_number,
                'restlen' => 0,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ];
            DB::connection('newWarranty')
                ->table('roll_restlen')
                ->insert($data);
        }
    }

    /*
     * 批量删除*/
    public function delB()
    {
        $wids = [100672126,100671410,10111817,100671754,100671706,100672155,100672126,100671621,100672127,100672093,100672094,100672062,100672117,100672119,100671285,100672114,100671216,100672087,100672091,100672063,100672118,100670750,100671652,100671675,100671664,100671676,100671691,100671707,100671630,100671631,100671639,100671653,100672083,100671735,100671736,100671665,100671677,100672086,100671684,100672068,100671000,100671640,100671708,100671709,100671647,100671654,100671641,100671655,100671667,100671686,100671666,100671737,100671668,100671755,100671669,100671693,100671698,100672104,100671738,100671739,100672120,100671632,100672107,100671648,100671670,100671687,100671717,100671722,100671756,100672099,100671757,100671758,100671633,100671649,100672075,100671656,100672076,100671671,100672077,100671672,100671678,100671688,100671694,100671699,100671718,100672061,100672072,100672108,100672095,100672102,100672081,100672109,100672116,100672096,100672078,100671759,100671760,100671761,100671762,100670861,100671663,100671763,100671764,100671741,100671742,100671743,100672123,100671744,100670843,100671645,100671745,100671746,100671747,100671748,100671749,100671723,100671724,100671725,100671726,100671727,100671728,100671729,100671730,100671731,100671733,100671734,100671719,100671720,100671721,100671711,100671712,100671713,100671714,100671715,100671716,100672064,100671700,100671701,100671702,100671704,100671689,100671679,100671680,100671681,100671682,100671673,100671674,100671750,100671751,100671657,100671658,100671659,100671660,100671661,100671650,100671634,100671635,100671636,100671637,100671638,100671620,100671621,100672127,100671622,100671623,100671624,100671625,100671626,100671627,100671628,100671591,100671559,100671696,100672084,100672082,100671525,100671526,100671521,100671522,100671523,100672112,100671524,100672110,100672105,100671350,100672089,100672115,100672065,100671772,100672122,100671350,100671770,100671767,100671766,100672121,100672111,100672092,100672074,100672058,100672060,100671053,100672106,100672097,100672098,100672100,100672101,100672103,100672085,100671132,100672080,100672073,100672070,100672071,100672088,100671299,100672066,100671752,100671705,100671695,100671559,100671696,100671690,100672069,100671683,100671753,100671662,100671651,100671644,100671629,100671774,100671771,100671769,100671768,100671765];

        $wvc = DB::connection('mysql')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'w.id', 'wvc.warranty_id')
            ->whereIn('wvc.warranty_id', $wids)
            ->whereNull('wvc.deleted_at')
            ->get(['w.id', 'w.user_id', 'wvc.roll_number', 'wvc.length']);

        DB::connection('mysql')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->whereIn('warranty_id', $wids)
            ->update([
                'deleted_at' => date('Y-m-d H:i:s')
            ]);
        DB::connection('mysql')
            ->table('warranty')
            ->whereIn('id', $wids)
            ->whereNull('deleted_at')
            ->update([
                'deleted_at' => date('Y-m-d H:i:s')
            ]);

        $rl = [];
        foreach ($wvc as $k => $v) {
            $rl = $this->plusLength($v->user_id, $v->roll_number, $v->length);
        }
        return [];
    }

    /*
     * 清除表数据sql语句，供参考*/
    public function truncateTable()
    {
        $sql1 = 'truncate table precut';
        $sql1 = 'truncate table precut_assign';
        $sql1 = 'truncate table precut_to_vehicle_coverage';
        $sql1 = 'truncate table roll_master';
        $sql1 = 'truncate table roll_restlen';
        $sql1 = 'truncate table roll_return';
        $sql1 = 'truncate table roll_sales';
        $sql1 = 'truncate table roll_transfer';
        $sql1 = 'truncate table warranty';
        $sql1 = 'truncate table warranty_image';
        $sql1 = 'truncate table warranty_logs';
        $sql1 = 'truncate table warranty_msg';
        $sql1 = 'truncate table warranty_to_vehicle_coverage';
        $sql1 = 'truncate table warranty_verify';
    }

    public function updateWarranty()
    {
        $sql = 'update warranty as w '
            .'left join users as u on u.id=w.user_id'
            .'left join users as y on u.creator_id=y.id'
            .'set w.dealer_name=u.abbr,'
            .'w.distributor_id=if(u.user_type<3, u.id, y.id),'
            .'w.distributor_name=if(u.user_type<3, u.abbr, y.abbr)'; // 更新质保省代、经销商信息
    }

    public function updateUsers2()
    {
        $sql = 'update users set creator_id=2244 where province_id=1 and creator_id=72'; // 北京更新为北京省代
        $sql = 'update users set creator_id=389 where province_id=2 and creator_id=72'; // 天津更新为天津省代
    }

    public function updateUsers3()
    {
        $user = DB::connection('newWarranty')
            ->table('users')
            ->where('user_type', '=', 2)
            ->where('status', 1)
            ->whereNull('deleted_at')
            ->get();
        $uids = Arr::pluck($user, 'id');

        $password = 'fPlrsJbpbZzh5VOm';
        $password = bcrypt($password);
        $user = DB::connection('newWarranty')
            ->table('users')
            ->whereIn('id', $uids)
            ->update(['password' => $password]);
        return $uids;

    }

    public function updateUsers()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 01:01:01';
        $user = DB::connection('newWarranty')
            ->table('users')
            ->where('user_type', '!=', 3)
            ->where('status', 1)
            ->whereNull('deleted_at')
            ->get();
        $uids = Arr::pluck($user, 'id');
        $data = DB::connection('oldWarranty')
            ->table('users')
            ->whereNotIn('user_id', $uids)
            ->get();
        $array = ['id', 'creator_id', 'user_type', 'unique_id', 'company_name', 'abbr', 'first_name', 'last_name', 'username', 'email_address', 'password', 'address', 'city', 'province_id', 'zip', 'region_id', 'phone_number', 'note', 'cell_phone', 'wechat_id', 'date_signedup', 'additional_comment', 'province', 'status', 'mdtype', 'language_id', 'extension', 'created_at', 'updated_at', 'deleted_at'];
        $upload = [];
        foreach ($data as $k=>$v){
            $v->id = $v->user_id;
            $v->abbr = $v->company_name;
            $v->mdtype = 0;
            if ($v->date_signedup < $bdate) {
                $v->date_signedup = $bdate;
            }
            $v->created_at = $v->date_signedup;
            $v->updated_at = $v->date_signedup;
            if ($v->user_type == 2) {
                $v->deleted_at = $tdate;
            } else {
                $v->deleted_at = null;
            }
            $item = [];
            foreach ($array as $ak => $av) {
                $item[$av] = $v->$av;
            }
            $upload[] = $item;
            /*$item = array_values($item);
            $users[$k] = array_values($item);*/
        }

        $bool = DB::connection('newWarranty')
            ->table('users')
            ->whereNotIn('id', $uids)
            ->delete();
        $up = [];
        foreach ($upload as $k => $v) {
            if ($k && !($k%1000)) {
                $b2 = DB::connection('newWarranty')
                    ->table('users')
                    ->insert($up);
                $up = [];
            }
            $up[] = $v;
        }
        if (!empty($up)) {
            $b4 = DB::connection('newWarranty')
                ->table('users')
                ->insert($up);
        }

        return [$bool, $b2,$b4];
    }

    /*
     * 由于默认状态为0
     * 现将所有卷号状态更新为2，已分配
     * 根据sql查询，当管理员剩余长度等于卷总长度时，跟新为0
     * 根据sql查询，当剩余长度之和等于总长度，且状态为2时，更新为1*/
    public function updateRollStatus()
    {
        $sql1 = 'update roll_master set status=2';
        $sql2 = 'update roll_master as rm left join roll_restlen as rr on rm.roll_number=rr.roll_number set status=0 where rr.restlen=rm.length';
        $sql3 = 'update roll_master as rm left join (select sum(restlen) as restlen from roll_restlen group by roll_number) as rr on rm.roll_number=rr.roll_number set status=1 where rr.restlen=rm.length and rm.status=2';
    }
    /*
     * 执行该方法获取sql文件导入数据库
     * 手动更新sequences表precut next改为旧表precut_kit next的值
     * */
    public function migrate()
    {

        $sql1 = $this->wtRolls(); // 安装部位
        $sql2 = $this->wtCoverage(); //  卷号以及分配记录
        $sql3 = $this->wtPrecut();
        $sql4 = $this->wtWarranty();
        $sql5 = $this->rest1123();
        $sql = $sql1 . $sql2 .  $sql3 . $sql4 . $sql5;

        $sql = str_replace('\t', '', $sql);
        $sql = str_replace('\n', '', $sql);
        $sql = str_replace('\/', '', $sql);
        $filename = __DIR__.'/migration_'.date('YmdHi').'.sql';
        file_put_contents($filename, $sql);
        return [$filename];
    }

    /*
     * 更新剩余长度*/
    public function rest1123()
    {
        $um1 = [];
        $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->select([DB::raw('roll_id as id'), 'roll_id', 'roll_number', 'length'])
            ->orderBy('roll_id')
            ->get();
        $rm = [];
        $rno = [];
        foreach ($data as $k => $v) {
            $key = $v->roll_number;
            $rm[$key] = $v->id;
            $rno[$v->id] = $key;
            $um1['1###'.$key] = $v->length;
        }

        $pvc = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage as pvc')
            ->leftJoin('precut_kit as p', 'pvc.precut_kit_sale_id', 'p.precut_autoincrement_id')
            ->select(['roll_number', 'length', 'p.precut_autoincrement_id as pid', 'user_id',  DB::raw('user_id as created_by')])
            ->get();
        $rus = []; // 主数组
        foreach ($pvc as $k => $v) {
            if (empty($v->pid)) {
                continue;
            }
            if (empty($v->created_by)) {
                $key = '1###'.$v->roll_number;
            } else {
                $key = $v->created_by .'###'.$v->roll_number;
            }
            $rus[$key] = @$rus[$key] + $v->length;
        }

        $wu = [];
        $wvc = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'wvc.warranty_id', 'w.warranty_id')
            ->leftJoin('precut_kit as p', 'w.pre_id', 'p.precut_autoincrement_id')
            ->select(['wvc.roll_number', 'wvc.length', 'p.precut_autoincrement_id as pid', 'w.warranty_id as wid', 'w.user_id'])
            ->get();
        foreach ($wvc as $k => $v) {
            if (empty($v->wid)) {
                continue;
            }
            if (!empty($v->pid)) {
                continue;
            }
            if (empty($v->user_id)) {
                $key = '1###'.$v->roll_number;
            } else {
                $key = $v->user_id .'###'.$v->roll_number;
            }
            $rus[$key] = @$rus[$key] + $v->length;
        }
        // return [array_slice($rus, 0, 1)];

        foreach ($rus as $k => $v) {
            if (empty($um1[$k])) {
                $um1[$k] = 0;
            }
            $um1[$k] = $um1[$k] - $v;
        }

        // return [count($um1)];

        /*销售表*/
        $rsb = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->select(['roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold'])
            ->orderBy('date_sold')
            ->get();

        /*
         * 最后一个拥有全部，含负数*/
        foreach ($rsb as $k => $v) {
            $roll_number = @$rno[$v->roll_id];
            if (empty($roll_number)) {
                continue;
            }
            $key1 = $v->sold_to_user_id.'###'.$roll_number;
            $key2 = $v->sold_by_user_id.'###'.$roll_number;

            $um1[$key1] = @$um1[$key1] + @$um1[$key2]; // 分配对象
            $um1[$key2] = 0; // 被分配对象，清零
        }

        $um2 = $um1;
        $dts = [];
        $urdts = [];

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            if (empty($urdts[$ka[1]])) {
                $urdts[$ka[1]] = [];
            }
            $urdts[$ka[1]][] = $ka[0];
        }

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            $dts = [
                'uid' => $ka[0],
                'rn' => $ka[1],
                'len' => $v,
            ];
            if ($v >= 0) {
                continue;
            }
            if ($ka[0] == 1) {
                continue;
            }
            $aka = '1###'.$ka[1];
            if (empty($um1[$aka])) {
                continue;
            }
            $len = $um1[$aka] + $v;
            if ($len >= 0) {
                $um1[$k] = 0;
                $um1[$aka] = $len;
            } else {
                $um1[$k] = $len;
                $um1[$aka] = 0;
                foreach ($urdts[$ka[1]] as $rk => $rv) {
                    $rkr = $rv.'###'.$ka[1];
                    if ($um1[$rkr] > 0 && $um1[$k] < 0) {
                        $len2 = $um1[$rkr] + $um1[$k];
                        if ($len2 >= 0) {
                            $um1[$k] = 0;
                            $um1[$rkr] = $len2;
                        } else {
                            $um1[$k] = $len2;
                            $um1[$rkr] = 0;
                        }
                    }
                }
            }
        }
        $diff = [];
        foreach ($um1 as $k => $v) {
            if ($um2[$k] != $v) {
                $diff[$k] = [
                    'old' => $um2[$k],
                    'new' => $v
                ];
            }
        }
        $upload = [];
        $tdate = date('Y-m-d H:i:s');
        foreach ($um1 as $k => $v) {
            $ar = explode('###', $k, 2);
            $item = [
                'user_id' => @$ar[0] ?: 0,
                'roll_number' => $ar[1],
                'restlen' => $v ?: 0,
                'created_at' => $tdate,
                'updated_at' => $tdate,
            ];
            $upload[] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO roll_restlen (`user_id`, `roll_number`, `restlen`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";
        return $sql;
    }

    protected function wtRolls()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $count = $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->count();
        $m = ceil($count/10000);
        $sql = '';
        // $rno = [];  // ID对应卷号
        $roll_length = []; // ID长度
        // $rno_length = [];   // 卷号长度
        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('roll_master')
                ->select(['roll_id as id', 'roll_id', 'roll_number', 'film_type_id', 'width', 'length', 'product_type_id', 'date_entered'])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('roll_id')
                ->get();
            $upload = [];
            $array = ['id', 'roll_number', 'film_type_id', 'width', 'length', 'product_type_id', 'created_at', 'updated_at'];
            foreach ($data as $k=>$v){
                // $rno[$v->id] = $v->roll_number;
                $roll_length[$v->id] = (int) $v->length;
                // $rno_length[$v->roll_number] = $v->length;
                if (strtotime($v->date_entered) < strtotime($bdate)) {
                    $v->date_entered = $bdate;
                } else {
                    $v->date_entered = date('Y-m-d H:i:s', strtotime($v->date_entered));
                }
                $v->length = (int) $v->length;
                $v->created_at = $v->date_entered;
                $v->updated_at = $v->date_entered;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    /*if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }*/
                    $text = html_entity_decode($text);
                    $text = str_replace('\t', '', $text);
                    $text = str_replace('\n', '', $text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO roll_master (`id`, `roll_number`, `film_type_id`, `width`, `length`, `product_type_id`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }

        $count = $data = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->count();
        $m = ceil($count/10000);

        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('roll_sales')
                ->select(['roll_sales_id as id', 'roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold', 'is_precut'])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('roll_sales_id')
                ->get();
            $upload = [];
            $array = ['id', 'roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold', 'is_precut', 'length', 'type', 'created_at', 'updated_at', 'created_by'];
            foreach ($data as $k=>$v){
                if (strtotime($v->date_sold) < strtotime($bdate)) {
                    $v->date_sold = $bdate;
                } else {
                    $v->date_sold = date('Y-m-d H:i:s', strtotime($v->date_sold));
                }
                $v->created_at = $v->date_sold;
                $v->updated_at = $v->date_sold;
                $v->length = (int) @$roll_length[$v->roll_id] ?: 0;
                $v->type = 1;
                $v->created_by = $v->sold_by_user_id;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    /*if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }*/
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO roll_sales (`id`, `roll_id`, `sold_by_user_id`, `sold_to_user_id`, `date_sold`, `is_precut`, `length`, `type`, `created_at`, `updated_at`, `created_by`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }

        return $sql;
    }

    protected function wtCoverage()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $sql = '';
        /*
         * 质保安装部位*/
        $count = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage')
            ->count();
        $m = ceil($count/10000);
        $data = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage')
            ->select(['vehicle_coverage_id as id', 'warranty_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width','length'])
            ->get();
        $array = ['id', 'warranty_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width', 'length', 'created_at', 'updated_at'];
        $upload = [];
        foreach ($data as $k=>$v){
            $v->created_at = $bdate;
            $v->updated_at = $bdate;
            $v->length = (int) $v->length;
            $v->length = $v->length >= 0 ? $v->length : 0;

            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO warranty_to_vehicle_coverage (`id`, `warranty_id`, `part_id`, `precut_or_manual`, `roll_number`, `film_type_id`, `width`, `length`, `created_at`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";

        /*
         * 专车专用套件*/
        $data = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage')
            ->select(['vehicle_coverage_id as id', 'precut_kit_sale_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width','length'])
            ->get();
        $array = ['id', 'precut_kit_sale_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width', 'length', 'created_at', 'updated_at'];
        $upload = [];
        foreach ($data as $k=>$v){
            $v->created_at = $bdate;
            $v->updated_at = $bdate;
            $v->length = (int) $v->length;
            $v->length = $v->length >= 0 ? $v->length : 0;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO precut_to_vehicle_coverage (`id`, `precut_kit_sale_id`, `part_id`, `precut_or_manual`, `roll_number`, `film_type_id`, `width`, `length`, `created_at`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";
        return $sql;
    }

    protected function wtPrecut()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $sql = '';
        $count = $data = DB::connection('oldWarranty')
            ->table('precut_kit')
            ->count();
        $m = ceil($count/10000);

        // 套件分配
        $data = DB::connection('oldWarranty')
            ->table('precut_kit_sales')
            ->select([
                'precut_kit_sale_id as id',
                'precut_autoincrement_id as precut_id',
                'sold_by_user_id',
                'sold_to_user_id',
                'date_sold'
            ])
            ->orderBy('precut_kit_sale_id')
            ->get();
        $upload = [];
        $array = [
            'id',
            'precut_id',
            'sold_by_user_id',
            'sold_to_user_id',
            'type',
            'created_at',
            'created_by',
            'updated_at'
        ];
        $sold = [];
        foreach ($data as $k=>$v){
            $sold[$v->precut_id] = $v->sold_to_user_id;
            if (strtotime($v->date_sold) < strtotime($bdate)) {
                $v->date_sold = $bdate;
            } else {
                $v->date_sold = date('Y-m-d H:i:s', strtotime($v->date_sold));
            }
            $v->created_at = $v->date_sold;
            $v->updated_at = $v->date_sold;
            $v->type = 1;
            $v->created_by = $v->sold_by_user_id;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                /*if (mb_stripos($text, '&#')===0) {
                    $text = str_replace('&#', ';&#', $text).';';
                    $text = mb_substr($text, 1);
                }*/
                $text = html_entity_decode($text);
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO precut_assign (`id`, `precut_id`, `sold_by_user_id`, `sold_to_user_id`, `type`, `created_at`, `created_by`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";
        // 套件
        $wpk = DB::connection('oldWarranty')
            ->table('warranty')
            ->select('pre_id', 'warranty_id')
            ->get();
        $wpks = [];
        foreach ($wpk as $k => $v) {
            $wpks[$v->pre_id] = 1;
        }
        $data = DB::connection('oldWarranty')
            ->table('precut_kit as pk')
            // ->leftJoin('warranty as w', 'w.pre_id', 'pk.precut_autoincrement_id')
            //  as id
            ->select([
                'pk.precut_autoincrement_id as id',
                'pk.user_id',
                'pk.precut_id',
                'pk.year_id',
                'pk.make_id',
                'pk.model_id',
                'pk.installed',
                'pk.created_date',
                'pk.created_date as created_at',
                'pk.user_id as created_by',
                'pk.created_date as updated_at'
            ])
            // ->groupBy('pk.precut_autoincrement_id')
            ->get();
        $upload = [];
        $array = [
            'id',
            'user_id',
            'precut_id',
            'year_id',
            'make_id',
            'model_id',
            'status',
            'created_at',
            'created_by',
            'updated_at'
        ];
        foreach ($data as $k => $v) {
            $item = [];
            if (strtotime($v->created_date) < strtotime($bdate)) {
                $v->created_date = $bdate;
            } else {
                $v->created_date = date('Y-m-d H:i:s', strtotime($v->created_date));
            }
            // $v->id = $v->precut_autoincrement_id;
            $v->created_at = $v->created_date;
            $v->updated_at = $v->created_date;
            $v->type = 1;
            $v->created_by = $v->user_id;
            $v->user_id = @$sold[$v->id] ?: 0;
            $v->model_id = mb_substr($v->model_id, 0, 200);
            // $v->status = $v->installed == 'Yes' ? 1 : 0;
            if (@$wpks[$v->id]) {
                $v->status = 1;
            } else {
                $v->status = 0;
            }
            /*if ($v->warranty_id) {
                $v->status = 1;
            } else {
                $v->status = 0;
            }*/
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                /*if (mb_stripos($text, '&#')===0) {
                    $text = str_replace('&#', ';&#', $text).';';
                    $text = mb_substr($text, 1);
                }*/
                $text = html_entity_decode($text);
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO precut (`id`, `user_id`, `precut_id`, `year_id`, `make_id`, `model_id`, `status`, `created_at`, `created_by`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";

        return $sql;
    }

    protected function wtWarranty()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $count = $data = DB::connection('oldWarranty')
            ->table('warranty')
            ->count();
        $limit = 10000;
        $m = ceil($count/$limit);
        $sql = '';

        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('warranty')
                ->select(['warranty_id as id', 'warranty_id', 'user_id', 'first_name', 'last_name', 'address', 'city', 'province_id', 'zip', 'region_id', 'email_address', 'phone_number', 'installer_name', 'installation_date', 'installation_price', 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side', 'year_id', 'make_id', 'model_id', 'sub_model_id', 'license_plate', 'vin_number', 'additional_comments', 'vehicle_coverage_id', 'pre_id', 'language_id', 'extension', 'user_id as fq_user', 'installation_date as fq_date', DB::raw('1 as approved'), DB::raw('1 as approved_by_user_id'), 'installation_date as approved_date', DB::raw('"'.$tdate.'" as created_at'), DB::raw('"'.$tdate.'" as updated_at')])
                ->limit($limit)
                ->offset($i*$limit)
                ->orderBy('warranty_id')
                ->get();
            $upload = [];
            $array = ['id', 'warranty_id', 'user_id', 'first_name', 'last_name', 'address', 'city', 'province_id', 'zip', 'region_id', 'email_address', 'phone_number', 'installer_name', 'installation_date', 'installation_price', 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side', 'year_id', 'make_id', 'model_id', 'sub_model_id', 'license_plate', 'vin_number', 'additional_comments', 'vehicle_coverage_id', 'pre_id', 'language_id', 'extension', 'fq_user', 'fq_date', 'approved', 'approved_by_user_id', 'approved_date', 'created_at', 'updated_at'];
            $imagse = [];
            foreach ($data as $k=>$v){
                if ($v->id == 0) {
                    $v->id = 1;
                    $v->warranty_id = 1;
                }
                if (strtotime($v->installation_date) < strtotime($bdate)) {
                    $v->installation_date = $bdate;
                } else {
                    $v->installation_date = date('Y-m-d H:i:s', strtotime($v->installation_date));
                }
                $v->fq_date = $v->installation_date;
                $v->approved_date = $v->installation_date;
                $v->model_id = mb_substr($v->model_id, 0, 200);
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    /*if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }*/
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
                foreach ([ 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side'] as $ik =>$iv) {
                    if (!empty($v->$iv)) {
                        $imagse_item = [
                            'warranty_id' => $v->id,
                            'part_id' => $ik + 1,
                            'image_src' => $v->$iv,
                            'created_at' => $tdate,
                            'updated_at' => $tdate,
                        ];
                        $imagse[] = array_values($imagse_item);
                    }
                }
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO warranty (`id`, `warranty_id`, `user_id`, `first_name`, `last_name`, `address`, `city`, `province_id`, `zip`, `region_id`, `email_address`, `phone_number`, `installer_name`, `installation_date`, `installation_price`, `image_front`, `image_back`, `image_driver_side`, `image_passenger_side`, `year_id`, `make_id`, `model_id`, `sub_model_id`, `license_plate`, `vin_number`, `additional_comments`, `vehicle_coverage_id`, `pre_id`, `language_id`, `extension`, `fq_user`, `fq_date`, `approved`, `approved_by_user_id`, `approved_date`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
            $data = array_values($imagse);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO warranty_image (`warranty_id`, `part_id`, `image_src`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }
        return $sql;
    }

    /**
     * 修复为负数的数据
     */
    public function batchProcessRollNumber()
    {
        set_time_limit(0);
        $sql = 'select sum(restlen) as total, roll_number from roll_restlen where roll_number in (select roll_number from roll_restlen where  restlen<0 group by roll_number) GROUP BY roll_number HAVING total>0';
        $data = DB::select($sql);
        if (empty($data)) {
            return;
        }

        foreach ($data as $item) {
            $rollNumberData = DB::table('roll_restlen')->where('roll_number', $item->roll_number)->get();
            if (empty($rollNumberData)) {
                continue;
            }
            DB::beginTransaction();
            foreach ($rollNumberData as $value) {
                $updateData = ['deleted_at' => null];
                if ($value->restlen < 0) {
                    $updateData['restlen'] = 0;
                }
                if ($value->restlen > 0) {
                    $updateData['restlen'] = $item->total;
                }
                $result = DB::table('roll_restlen')->where('id', $value->id)->update($updateData);
                if (!$result) {
                    DB::rollBack();
                    continue;
                }
            }
            $result = DB::table('roll_master')->where('roll_number', $item->roll_number)->update(['deleted_at' => null]);
            if (!$result) {
                DB::rollBack();
                continue;
            }
            DB::commit();
        }
    }
}
